﻿using Lima.ResumeSys.Class;
using Lima.ResumeSys.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lima.ResumeSys.DAL
{
    public class OperateDAL
    {
        static string exePath = GetApplicationRoot();//数据库在本程序中所在路径
        static string Psd = "LM8800";


        public static string GetApplicationRoot()
        {
            // 优先使用BaseDirectory，因为它更可靠
            string root = AppDomain.CurrentDomain.BaseDirectory;

            // 如果是调试环境，可能需要特殊处理
            if (Debugger.IsAttached && root.Contains("bin\\Debug"))
            {
                // 向上移动两级到项目目录
                root = Path.GetFullPath(Path.Combine(root, @"..\..\"));
            }

            return root + @"Data\";
        }

        public string Main(string path)
        {
            // 数据库文件路径（请根据实际情况修改）
            string sourceMdbPath = @"provider=Microsoft.Jet.OLEDB.4.0;data source=" + exePath + "SystemDate.mdb;" + "Jet OLEDB:Database Password=" + Psd;
            string targetMdbPath = @"provider=Microsoft.Jet.OLEDB.4.0;data source=" + path + ";" + "Jet OLEDB:Database Password=" + Psd;

            // 连接字符串
            string sourceConnectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={sourceMdbPath};";
            string targetConnectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={targetMdbPath};";

            // 要操作的表名数组
            string[] tablesNames = { "PersonInfo", "RewardPunish", "PerFile" };
            string strmessage = "";
            try
            {
                Console.WriteLine("开始多表数据导入过程...");

                // 遍历处理每个表
                foreach (string tableName in tablesNames)
                {
                    Console.WriteLine($"\n正在处理表: {tableName}");
                    string primaryKeyName = GetPrimaryKeyName(sourceConnectionString, tableName);
                    if (string.IsNullOrEmpty(primaryKeyName))
                    {
                        Console.WriteLine($"警告: 表 {tableName} 没有找到主键，将使用第一个字段");
                        primaryKeyName = GetFirstFieldName(sourceConnectionString, tableName);
                    }
                    Console.WriteLine($"使用主键字段: {primaryKeyName}");

                    // 第一步：根据主键删除目标表中的对应数据
                    DeleteMatchingData(sourceConnectionString, targetConnectionString, tableName, primaryKeyName);

                    // 第二步：从源表导入所有数据
                    ImportAllData(sourceConnectionString, targetConnectionString, tableName);
                    strmessage += $"表 {tableName} 处理完成\n";
                }

                return strmessage;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"发生错误: {ex.Message}");
                return "上传失败！";
            }
        }
        public string GetPrimaryKeyName(string connStr, string tableName)
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();

                // 获取主键信息
                DataTable schemaTable = conn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Primary_Keys,
                    new object[] { null, null, tableName });

                if (schemaTable.Rows.Count == 0)
                    return null;

                // 返回主键字段名
                return schemaTable.Rows[0]["COLUMN_NAME"].ToString();
            }
        }

        public string GetFirstFieldName(string connStr, string tableName)
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();

                // 获取表的架构信息
                DataTable schemaTable = conn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Columns,
                    new object[] { null, null, tableName, null });

                if (schemaTable.Rows.Count == 0)
                    throw new Exception($"表 {tableName} 不存在或没有字段");

                // 返回第一个字段名
                return schemaTable.Rows[0]["COLUMN_NAME"].ToString();
            }
        }

        public void DeleteMatchingData(string sourceConnStr, string targetConnStr, string tableName, string fieldName)
        {
            // 获取源表中的所有ID值
            DataTable sourceIds = new DataTable();

            using (OleDbConnection sourceConn = new OleDbConnection(sourceConnStr))
            {
                sourceConn.Open();
                string selectSql = $"SELECT [{fieldName}] FROM [{tableName}]";

                using (OleDbCommand cmd = new OleDbCommand(selectSql, sourceConn))
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
                {
                    adapter.Fill(sourceIds);
                }
            }

            if (sourceIds.Rows.Count == 0)
            {
                Console.WriteLine("源表没有数据，无需删除");
                return;
            }

            // 在目标表中删除匹配的记录
            using (OleDbConnection targetConn = new OleDbConnection(targetConnStr))
            {
                targetConn.Open();

                using (OleDbTransaction transaction = targetConn.BeginTransaction())
                {
                    try
                    {
                        // 构建IN语句的参数化查询（更高效的方式）
                        string paramNames = string.Join(",", sourceIds.Rows.Cast<DataRow>()
                            .Select((row, index) => "?"));

                        string deleteSql = $"DELETE FROM [{tableName}] WHERE [{fieldName}] IN ({paramNames})";

                        using (OleDbCommand cmd = new OleDbCommand(deleteSql, targetConn, transaction))
                        {
                            // 添加所有参数
                            for (int i = 0; i < sourceIds.Rows.Count; i++)
                            {
                                cmd.Parameters.AddWithValue($"p{i}", sourceIds.Rows[i][fieldName]);
                            }

                            int totalDeleted = cmd.ExecuteNonQuery();
                            Console.WriteLine($"已删除 {totalDeleted} 条匹配记录");
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"删除数据时出错: {ex.Message}");
                        throw;
                    }
                }
            }
        }

        public void ImportAllData(string sourceConnStr, string targetConnStr, string tableName)
        {
            using (OleDbConnection sourceConn = new OleDbConnection(sourceConnStr))
            using (OleDbConnection targetConn = new OleDbConnection(targetConnStr))
            {
                sourceConn.Open();
                targetConn.Open();

                // 检查目标表是否存在
                if (!TableExists(targetConn, tableName))
                {
                    CloneTableStructure(sourceConn, targetConn, tableName);
                }

                // 使用事务确保操作的原子性
                using (OleDbTransaction transaction = targetConn.BeginTransaction())
                {
                    try
                    {
                        // 导入所有数据的SQL
                        string importSql = $@"
                        INSERT INTO [{tableName}] 
                        SELECT * FROM [{tableName}] IN '{sourceConn.DataSource}'";

                        using (OleDbCommand cmd = new OleDbCommand(importSql, targetConn, transaction))
                        {
                            int rowsAffected = cmd.ExecuteNonQuery();
                            Console.WriteLine($"成功导入 {rowsAffected} 条记录");
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"导入数据时出错: {ex.Message}");
                        throw;
                    }
                }
            }
        }

        public bool TableExists(OleDbConnection conn, string tableName)
        {
            var schema = conn.GetOleDbSchemaTable(
                OleDbSchemaGuid.Tables,
                new object[] { null, null, tableName, "TABLE" });

            return schema.Rows.Count > 0;
        }

        public void CloneTableStructure(OleDbConnection sourceConn, OleDbConnection targetConn, string tableName)
        {
            string createTableSql = $@"
            SELECT * INTO [{tableName}] 
            FROM [{tableName}] IN '{sourceConn.DataSource}'
            WHERE 1=0";

            using (OleDbCommand cmd = new OleDbCommand(createTableSql, targetConn))
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine($"已创建表 {tableName} 的结构");
            }
        }
    }
}
